#!/bin/bash
#
#function:
#	deploy mysql5.7.22 service on Centos7.2
#release:
#	2018-03-01 first-release by edwardmli|2018-04-18 second-release by edwardmli
#	modify yihuachen
#

SHELL_FOLDER=$(cd "$(dirname "$0")";pwd)
BACKUP_SCRIPTS_DIR='/data/db_backup/scripts'
MBUFFER_DEFAULT={{ MYSQL_BUFFER }}
MPORT_DEFAULT={{ MYSQL_PORT }}
MYSQL_PASS={{ MYSQL_PASS }}
MDATADIR_DEFAULT={{ MYSQL_DATADIR }}
MASTER_HOST={{ ansible_default_ipv4.address }}
MYSQL_EDITION=mysql-5.7.29-linux-glibc2.12-x86_64
MYSQL_BINARY_FILE="${MYSQL_EDITION}.tar.gz"
LIBAIO_RPM_FILE='libaio-0.3.109-13.el7.x86_64.rpm'

# check mysql whether had running
MYSQL_RUNING=`ps -ef | grep mysqld | grep -v grep | grep ${MPORT_DEFAULT} |  wc -l`
if [[ $MYSQL_RUNING -eq 0 ]]
then
    cat /etc/passwd | grep ^mysql && userdel mysql > /dev/null 2>&1 || echo 'not has mysql user'
    cat /etc/group | grep ^mysql && groupdel mysql> /dev/null 2>&1 || echo 'not has mysql group'
    groupadd mysql
    useradd -r -s /bin/false -g mysql mysql
    MYSQL_PORT=${MPORT_DEFAULT}
    BUFFER_POOL_SIZE=${MBUFFER_DEFAULT}
    DATA_DIR=${MDATADIR_DEFAULT}
    if [[ -d ${DATA_DIR} ]]
    then
        if [[ "`ls ${DATA_DIR}|wc -l`" != "0" ]]
        then
	        echo "${DATA_DIR} is not empty"
          exit 1
        fi
    else
        mkdir -p ${DATA_DIR}
        chown mysql:mysql -R ${DATA_DIR}
    fi


    #查看所需文件
    echo -e "######\033[32mCheck files\033[0m......"
    if [[ ! -f ${SHELL_FOLDER}/my.cnf ]]
    then
        echo "No file my.cnf found!"
        exit 1
    fi
    if [[ ! -f ${SHELL_FOLDER}/slave_deploy.sh ]]
    then
        echo "No file slave_deploy.sh found!"
        exit 1
    fi
    if [[ ! -f ${SHELL_FOLDER}/initial_data.sql ]]
    then
        echo "No file initial_data.sql found!"
        exit 1
    fi
    echo -e "\033[32mDone.\033[0m"

    sed -i "s#@MYSQL_PORT#${MYSQL_PORT}#g" ${SHELL_FOLDER}/my.cnf
    sed -i "s#@DATA_DIR#${DATA_DIR}#g" ${SHELL_FOLDER}/my.cnf
    sed -i "s#@BUFFER_POOL_SIZE#${BUFFER_POOL_SIZE}#g" ${SHELL_FOLDER}/my.cnf
    sed -i 's/^sql_mode=.*/{{ MYSQL_MODE }}/g' ${SHELL_FOLDER}/my.cnf
    cp -a ${SHELL_FOLDER}/my.cnf{,.slave}
    sed -i "s#@SERVER_ID#${MYSQL_PORT}#g" ${SHELL_FOLDER}/my.cnf
    sed -i "s#@SERVER_ID#1${MYSQL_PORT}#g" ${SHELL_FOLDER}/my.cnf.slave

    if [[ ! -f ${SHELL_FOLDER}/${MYSQL_BINARY_FILE} ]]
    then
        echo "No file ${MYSQL_BINARY_FILE} found!"
        exit 1
    fi
    echo -e "######\033[32mCheck directories\033[0m......"
    if [[ -d /usr/local/mysql ]]
    then
        echo "Mysql program already exists!Backup /user/local/mysql to /usr/local/mysql.$(date +%y%m%d)......"
        rm -rf /usr/local/mysql.$(date +%y%m%d)
        mv /usr/local/mysql{,.$(date +%y%m%d)}
    fi
    echo -e "\033[32mDone.\033[0m"

    cd ${SHELL_FOLDER}

    #初始化数据库环境
    echo -e "######\033[32mconfigure database environment\033[0m......"
    rpm -qa|grep libaio|grep x86_64|grep -v libaio-devel > /dev/null
    if [[ $? -eq 1 ]]
    then
        if [[ ! -f ${SHELL_FOLDER}/${LIBAIO_RPM_FILE} ]]
        then
            echo "No file ${LIBAIO_RPM_FILE} found!"
            exit 1
        fi
        rpm -ivh ${LIBAIO_RPM_FILE}
    fi
    if [[ $? -eq 1 ]]
    then
        echo -e "libaio install \033[31mfailed!\033[0m"
        exit 1
    fi

    tar zxf ${MYSQL_BINARY_FILE} -C /usr/local/
    ln -s /usr/local/${MYSQL_EDITION} /usr/local/mysql
    cp -a /usr/local/mysql/bin/mysql* /usr/bin/
    cat /etc/sysctl.conf | grep 'vm.swappiness = 5' || echo 'vm.swappiness = 5' >> /etc/sysctl.conf
    cat /etc/sysctl.conf | grep 'vm.dirty_background_ratio = 5' || echo 'vm.dirty_background_ratio = 5' >> /etc/sysctl.conf
    cat /etc/sysctl.conf | grep 'vm.dirty_ratio = 10' || echo 'vm.dirty_ratio = 10' >> /etc/sysctl.conf
    cat /etc/sysctl.conf | grep 'net.ipv4.tcp_tw_recycle = 1' || echo 'net.ipv4.tcp_tw_recycle = 1' >> /etc/sysctl.conf
    cat /etc/sysctl.conf | grep 'net.ipv4.tcp_tw_reuse = 1' || echo 'net.ipv4.tcp_tw_reuse = 1' >> /etc/sysctl.conf
    sysctl -p /dev/null 2>&1

    echo -e "\033[32mDone.\033[0m"

    #安装数据库
    echo -e "######\033[32minstall mysql\033[0m......"
    cp -a my.cnf /etc/my.cnf
    /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize-insecure > /dev/null 2>&1
    chown -R mysql.mysql ${DATA_DIR}
    echo -e "\033[32mDone.\033[0m"

    #启动数据库
    echo -e "######\033[32mstart mysql service\033[0m......"
    mkdir -p /data/logs/mysql > /dev/null 2>&1
    chown -R mysql.mysql /data/logs/mysql
    nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf > /data/logs/mysql/mysqld_safe.log 2>&1 &
    sleep 30

    #检测数据库服务是否启动
    ######check mysql service######
    ps -ef|grep mysqld|grep -v grep|grep ${MYSQL_PORT} > /dev/null
    if [[ $? -eq 0 ]]
    then
      netstat -ntpl|grep ${MYSQL_PORT} > /dev/null
      if [[ $? -eq 1 ]]
      then
         echo -e "mysql installation \033[31mfailed!\033[0m"
	       exit 1
       fi
    else
       echo -e "mysql installation \033[31mfailed!\033[0m"
       exit 1
    fi
    echo -e "\033[32mDone.\033[0m"

    #限制root账号登录
    echo -e "######\033[32mClean users\033[0m......"
    /usr/local/mysql/bin/mysql -S ${DATA_DIR}/mysql.sock -uroot --default-character-set=utf8mb4 -e "delete from mysql.user where user='';delete from mysql.user where host<>'localhost';"
    if [[ $? -eq 1 ]]
    then
      echo -e "\033[31mfailed!\033[0m"
      exit 1
    fi
    echo -e "\033[32mDone.\033[0m"

    #生成初始化用户权限
    echo -e "######\033[32minitialize privileges\033[0m......"
    passw=${MYSQL_PASS}
    echo -e "\033[32mDone.\033[0m"
    #变更账号为system，并重置密码
    repl_passw=`cat /dev/urandom |tr -dc '0-9A-Za-z'|fold -w 10|head -1`
    echo -e "######\033[32mUpdate user password\033[0m......"
    /usr/local/mysql/bin/mysql -S ${DATA_DIR}/mysql.sock -uroot --default-character-set=utf8mb4 -e "set password=password('$passw');update mysql.user set user='system' where user='root';flush privileges;reset master;" > /dev/null 2>&1
    /usr/local/mysql/bin/mysql -S ${DATA_DIR}/mysql.sock -usystem -p$passw --default-character-set=utf8mb4 -e "grant replication slave on *.* to repl@'%' identified by '${repl_passw}';flush privileges;" > /dev/null 2>&1
    /usr/local/mysql/bin/mysql -S ${DATA_DIR}/mysql.sock -usystem -p$passw --default-character-set=utf8mb4 < ${SHELL_FOLDER}/initial_data.sql > /dev/null 2>&1
    if [[ $? -eq 1 ]]
    then
      echo -e "\033[31mfailed!\033[0m"
      exit 1
    fi
    echo -e "\033[32mDone.\033[0m"
    echo -e "######\033[32mmake slave installation script\033[0m......"
    sed -i "s#@BUFFER_POOL_SIZE#${BUFFER_POOL_SIZE}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@MYSQL_PORT#${MYSQL_PORT}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@DATA_DIR#${DATA_DIR}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@MYSQL_EDITION#${MYSQL_EDITION}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@LIBAIO_RPM_FILE#${LIBAIO_RPM_FILE}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@MASTER_PASS#${passw}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@MASTER_HOST#${MASTER_HOST}#g" ${SHELL_FOLDER}/slave_deploy.sh
    sed -i "s#@SLAVE_PASS#${repl_passw}#g" ${SHELL_FOLDER}/slave_deploy.sh

    tar czf wx_mysql_slave.tgz backup_script.sh libaio-0.3.109-13.el7.x86_64.rpm my.cnf.slave ${MYSQL_BINARY_FILE} slave_deploy.sh
    #clean files
    rm ${SHELL_FOLDER}/{backup_script.sh,initial_data.sql,libaio-0.3.109-13.el7.x86_64.rpm,master_deploy.sh,my.cnf,my.cnf.slave,${MYSQL_BINARY_FILE},slave_deploy.sh}
    echo -e "\033[32mDone.\033[0m"
    echo "Copy ${SHELL_FOLDER}/wx_mysql_slave.tgz to slave server and exec:
	  tar xzf wx_mysql_slave.tgz
	  sh slave_deploy.sh"

    echo -e "\033[32msuccessfully!\033[0m"
else
    echo "mysql had deploy and running now!"
fi
